package com.example.backend.repository;

import com.example.backend.domain.entity.Ticket;
import com.example.backend.domain.enums.TicketStatus;
import com.example.backend.domain.enums.TicketPriority;
import com.example.backend.domain.enums.TicketSource;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.Instant;
import java.util.List;
import java.util.Optional;

public interface TicketRepository extends JpaRepository<Ticket, Long>, JpaSpecificationExecutor<Ticket> {
    
    // 基础查询
    Page<Ticket> findByStatus(TicketStatus status, Pageable pageable);
    Page<Ticket> findByPriority(TicketPriority priority, Pageable pageable);
    Page<Ticket> findBySource(TicketSource source, Pageable pageable);
    Page<Ticket> findByCreatedBy_Id(Long userId, Pageable pageable);
    Page<Ticket> findByAssignedTo_Id(Long userId, Pageable pageable);
    Page<Ticket> findByCustomer_Id(Long customerId, Pageable pageable);
    
    // 复合查询
    Page<Ticket> findByStatusAndPriority(TicketStatus status, TicketPriority priority, Pageable pageable);
    Page<Ticket> findByAssignedTo_IdAndStatus(Long assignedToId, TicketStatus status, Pageable pageable);
    Page<Ticket> findByCustomer_IdAndStatus(Long customerId, TicketStatus status, Pageable pageable);
    
    // 按工单编号查询
    Optional<Ticket> findByTicketNumber(String ticketNumber);
    
    // 统计方法
    long countByStatus(TicketStatus status);
    long countByPriority(TicketPriority priority);
    long countBySource(TicketSource source);
    long countByCreatedAtAfter(Instant instant);
    long countByCreatedAtBetween(Instant start, Instant end);
    long countByUpdatedAtBetweenAndStatus(Instant start, Instant end, TicketStatus status);
    
    // 按时间范围查询
    @Query("SELECT t FROM Ticket t WHERE t.createdAt >= :start AND t.createdAt <= :end ORDER BY t.createdAt DESC")
    List<Ticket> findByCreatedAtBetween(@Param("start") Instant start, @Param("end") Instant end);
    
    // 超时工单查询 - 使用JOIN FETCH避免懒加载问题
    @Query("SELECT t FROM Ticket t LEFT JOIN FETCH t.assignedTo WHERE t.status = :status AND t.createdAt < :cutoffTime")
    Page<Ticket> findOverdueTickets(@Param("cutoffTime") Instant cutoffTime, @Param("status") TicketStatus status, Pageable pageable);
    
    // 即将到期的工单
    @Query("SELECT t FROM Ticket t WHERE t.dueDate IS NOT NULL AND t.dueDate <= :dueTime AND t.status IN :statuses ORDER BY t.dueDate ASC")
    List<Ticket> findUpcomingDueTickets(@Param("dueTime") Instant dueTime, @Param("statuses") List<TicketStatus> statuses);
    
    // 搜索工单 - 优化版
    @Query(value = "SELECT t.* FROM tickets t " +
           "LEFT JOIN users u ON t.assigned_to_id = u.id " +
           "WHERE (:keyword IS NULL OR " +
           "t.title LIKE CONCAT('%', :keyword, '%') OR " +
           "t.description LIKE CONCAT('%', :keyword, '%') OR " +
           "t.ticket_number LIKE CONCAT('%', :keyword, '%')) " +
           "AND (:status IS NULL OR t.status = :status) " +
           "AND (:priority IS NULL OR t.priority = :priority) " +
           "AND (:assignedToId IS NULL OR t.assigned_to_id = :assignedToId) " +
           "ORDER BY t.updated_at DESC",
           countQuery = "SELECT COUNT(*) FROM tickets t " +
           "LEFT JOIN users u ON t.assigned_to_id = u.id " +
           "WHERE (:keyword IS NULL OR " +
           "t.title LIKE CONCAT('%', :keyword, '%') OR " +
           "t.description LIKE CONCAT('%', :keyword, '%') OR " +
           "t.ticket_number LIKE CONCAT('%', :keyword, '%')) " +
           "AND (:status IS NULL OR t.status = :status) " +
           "AND (:priority IS NULL OR t.priority = :priority) " +
           "AND (:assignedToId IS NULL OR t.assigned_to_id = :assignedToId)",
           nativeQuery = true)
    Page<Ticket> searchTickets(@Param("keyword") String keyword,
                               @Param("status") String status,
                               @Param("priority") String priority,
                               @Param("assignedToId") Long assignedToId,
                               Pageable pageable);
    
    // 获取用户的工单统计
    @Query("SELECT t.status, COUNT(t) FROM Ticket t WHERE t.customer.id = :customerId GROUP BY t.status")
    List<Object[]> getTicketStatsByCustomer(@Param("customerId") Long customerId);
    
    @Query("SELECT t.status, COUNT(t) FROM Ticket t WHERE t.assignedTo.id = :assignedToId GROUP BY t.status")
    List<Object[]> getTicketStatsByAssignee(@Param("assignedToId") Long assignedToId);
    
    // 获取工单趋势数据 - 使用原生SQL查询以支持DATE函数
    @Query(value = "SELECT DATE(t.created_at) as date, COUNT(t.id) as count FROM tickets t " +
           "WHERE t.created_at >= :startDate AND t.created_at <= :endDate " +
           "GROUP BY DATE(t.created_at) ORDER BY date ASC",
           nativeQuery = true)
    List<Object[]> getTicketTrendData(@Param("startDate") Instant startDate, @Param("endDate") Instant endDate);
    
    // 获取工单状态分布
    @Query("SELECT t.status, COUNT(t) FROM Ticket t GROUP BY t.status")
    List<Object[]> getTicketStatusDistribution();
    
    // 获取工单优先级分布
    @Query("SELECT t.priority, COUNT(t) FROM Ticket t GROUP BY t.priority")
    List<Object[]> getTicketPriorityDistribution();
    
    // 获取平均解决时间
    @Query("SELECT AVG(TIMESTAMPDIFF(HOUR, t.createdAt, t.resolvedAt)) FROM Ticket t WHERE t.status = 'RESOLVED' AND t.resolvedAt IS NOT NULL")
    Double getAverageResolutionTimeInHours();
    
    // 获取SLA达成率
    @Query("SELECT COUNT(t) FROM Ticket t WHERE t.status = 'RESOLVED' AND t.resolvedAt IS NOT NULL " +
           "AND TIMESTAMPDIFF(HOUR, t.createdAt, t.resolvedAt) <= :slaHours")
    Long getSlaCompliantTickets(@Param("slaHours") Integer slaHours);
    
    @Query("SELECT COUNT(t) FROM Ticket t WHERE t.status = 'RESOLVED' AND t.resolvedAt IS NOT NULL")
    Long getTotalResolvedTickets();
    
    // 仪表板相关查询方法
    long countByAssignedTo(com.example.backend.domain.entity.User user);
    long countByAssignedToAndStatus(com.example.backend.domain.entity.User user, TicketStatus status);
    long countByAssignedToAndPriority(com.example.backend.domain.entity.User user, TicketPriority priority);
    long countByAssignedToAndCreatedAtAfter(com.example.backend.domain.entity.User user, Instant instant);
    
    long countByCustomer(com.example.backend.domain.entity.User user);
    long countByCustomerAndStatus(com.example.backend.domain.entity.User user, TicketStatus status);
    long countByCustomerAndPriority(com.example.backend.domain.entity.User user, TicketPriority priority);
    long countByCustomerAndCreatedAtAfter(com.example.backend.domain.entity.User user, Instant instant);
    
    List<Ticket> findTop10ByOrderByCreatedAtDesc();
    List<Ticket> findTop10ByAssignedToOrderByCreatedAtDesc(com.example.backend.domain.entity.User user);
    List<Ticket> findTop10ByCustomerOrderByCreatedAtDesc(com.example.backend.domain.entity.User user);
    
    // =============================================
    // 性能优化查询 - 使用JOIN FETCH避免N+1问题
    // =============================================
    
    /**
     * 获取所有工单（带关联数据）- 避免N+1查询
     * 使用LEFT JOIN FETCH加载所有关联的User和Category
     */
    @Query("SELECT DISTINCT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.createdBy " +
           "LEFT JOIN FETCH t.category " +
           "ORDER BY t.createdAt DESC")
    List<Ticket> findAllWithAssociations();
    
    /**
     * 按状态获取工单（带关联数据）
     */
    @Query("SELECT DISTINCT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.createdBy " +
           "LEFT JOIN FETCH t.category " +
           "WHERE t.status = :status " +
           "ORDER BY t.createdAt DESC")
    List<Ticket> findByStatusWithAssociations(@Param("status") TicketStatus status, Pageable pageable);
    
    /**
     * 按分配人ID获取工单（带关联数据）
     */
    @Query("SELECT DISTINCT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.createdBy " +
           "LEFT JOIN FETCH t.category " +
           "WHERE t.assignedTo.id = :assignedToId " +
           "ORDER BY t.updatedAt DESC")
    List<Ticket> findByAssignedToIdWithAssociations(@Param("assignedToId") Long assignedToId, Pageable pageable);
    
    /**
     * 按客户ID获取工单（带关联数据）
     */
    @Query("SELECT DISTINCT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.createdBy " +
           "LEFT JOIN FETCH t.category " +
           "WHERE t.customer.id = :customerId " +
           "ORDER BY t.createdAt DESC")
    List<Ticket> findByCustomerIdWithAssociations(@Param("customerId") Long customerId, Pageable pageable);
    
    /**
     * 按ID获取工单详情（带所有关联数据）
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.createdBy " +
           "LEFT JOIN FETCH t.resolvedBy " +
           "LEFT JOIN FETCH t.closedBy " +
           "LEFT JOIN FETCH t.category " +
           "WHERE t.id = :id")
    Optional<Ticket> findByIdWithAllAssociations(@Param("id") Long id);
    
    /**
     * 获取最近的工单（带关联数据）
     */
    @Query("SELECT DISTINCT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.category " +
           "ORDER BY t.createdAt DESC")
    List<Ticket> findRecentTicketsWithAssociations(Pageable pageable);
    
    // =============================================
    // DTO投影查询 - 只查询需要的字段，性能最优
    // =============================================
    
    /**
     * 获取工单列表（DTO投影）- 最优性能
     * 只查询列表页面需要的字段
     */
    @Query("SELECT new com.example.backend.dto.TicketListDTO(" +
           "t.id, t.ticketNumber, t.title, t.status, t.priority, " +
           "c.id, c.fullName, a.id, a.fullName, " +
           "cat.id, cat.categoryName, " +
           "t.createdAt, t.updatedAt, t.dueDate, t.isEscalated) " +
           "FROM Ticket t " +
           "LEFT JOIN t.customer c " +
           "LEFT JOIN t.assignedTo a " +
           "LEFT JOIN t.category cat " +
           "ORDER BY t.createdAt DESC")
    Page<com.example.backend.dto.TicketListDTO> findAllAsListDTO(Pageable pageable);
    
    /**
     * 按状态获取工单列表（DTO投影）
     */
    @Query("SELECT new com.example.backend.dto.TicketListDTO(" +
           "t.id, t.ticketNumber, t.title, t.status, t.priority, " +
           "c.id, c.fullName, a.id, a.fullName, " +
           "cat.id, cat.categoryName, " +
           "t.createdAt, t.updatedAt, t.dueDate, t.isEscalated) " +
           "FROM Ticket t " +
           "LEFT JOIN t.customer c " +
           "LEFT JOIN t.assignedTo a " +
           "LEFT JOIN t.category cat " +
           "WHERE t.status = :status " +
           "ORDER BY t.createdAt DESC")
    Page<com.example.backend.dto.TicketListDTO> findByStatusAsListDTO(@Param("status") TicketStatus status, Pageable pageable);
    
    /**
     * 按分配人获取工单列表（DTO投影）
     */
    @Query("SELECT new com.example.backend.dto.TicketListDTO(" +
           "t.id, t.ticketNumber, t.title, t.status, t.priority, " +
           "c.id, c.fullName, a.id, a.fullName, " +
           "cat.id, cat.categoryName, " +
           "t.createdAt, t.updatedAt, t.dueDate, t.isEscalated) " +
           "FROM Ticket t " +
           "LEFT JOIN t.customer c " +
           "LEFT JOIN t.assignedTo a " +
           "LEFT JOIN t.category cat " +
           "WHERE t.assignedTo.id = :assignedToId " +
           "ORDER BY t.updatedAt DESC")
    Page<com.example.backend.dto.TicketListDTO> findByAssignedToIdAsListDTO(@Param("assignedToId") Long assignedToId, Pageable pageable);
    
    /**
     * 按客户获取工单列表（DTO投影）
     */
    @Query("SELECT new com.example.backend.dto.TicketListDTO(" +
           "t.id, t.ticketNumber, t.title, t.status, t.priority, " +
           "c.id, c.fullName, a.id, a.fullName, " +
           "cat.id, cat.categoryName, " +
           "t.createdAt, t.updatedAt, t.dueDate, t.isEscalated) " +
           "FROM Ticket t " +
           "LEFT JOIN t.customer c " +
           "LEFT JOIN t.assignedTo a " +
           "LEFT JOIN t.category cat " +
           "WHERE t.customer.id = :customerId " +
           "ORDER BY t.createdAt DESC")
    Page<com.example.backend.dto.TicketListDTO> findByCustomerIdAsListDTO(@Param("customerId") Long customerId, Pageable pageable);
}



